-- EXEC SP_CREATE_UPDATE_DIEBANK_LOT 'TMS383B','F1B470.1',22600, null, '08/20/2009', null, 'Die Bank','UTAC',25, 'F1B470.1','Running',null, null, 'PROD','F1B470.1', null,null,'AdaptorLot'

Alter PROC SP_CREATE_UPDATE_DIEBANK_LOT (
 
 @PART_NUMBER 	VARCHAR(50), 
 @LOT_NUMBER 	VARCHAR(50), 
 @QTY_IN 	INT,
 @QTY_OUT 	INT,
 @DATE_IN 	DATETIME,
 @DATE_OUT 	DATETIME,
 @INVENTORY_TYPE VARCHAR(50),
 @VENDOR_NAME 	VARCHAR(50),
 @WAFER_QTY_IN 	INT,
 @VENDOR_LOTNUMBER VARCHAR(50),
 @STATUS 	VARCHAR(50),
 @WAFER_ID 	VARCHAR(100),
 @COMMENTS 	TEXT,
 @lot_category varchar(50),
 @fab_lot_number VARCHAR(50),
 @DATE_CODE		VARCHAR(50),
 @PO_NUMBER		VARCHAR(50),
 @LOT_FROM		VARCHAR(50)
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
	DECLARE @DIE_BANK_BIN_UPDATE INT
	DECLARE @LOT_ENTERED_BY	INT
 	DECLARE @P_TYPE VARCHAR(50)
	DECLARE @BINING_UPDATE INT
	DECLARE @LOT_DATEIN DATETIME
	DECLARE @ISBOM_PART VARCHAR(10)

	SET @LOT_ENTERED_BY = 1

	IF @LOT_FROM = 'dataEntry'
	BEGIN
		SET @LOT_ENTERED_BY = 0
	END
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end 	
	
	if @fab_lot_number is null
	begin
		set @fab_lot_number = @LOT_NUMBER
	end 
	
 	SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE PS_TYPE_NAME = @INVENTORY_TYPE AND P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
	
 	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 		SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE PMT,PROCESS_STEP PS WHERE PMT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @INVENTORY_TYPE AND PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID
 		IF @PROCESS_STEP_ID IS NULL
		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @lot_number AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status, TimeStamp )
					VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @inventory_type,'Discrepancy Lot Notification', @wafer_qty_in, @Qty_In, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
			END
										 			
 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 			RETURN
		END
	END
 				
 	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS, @LOT_ID = LOT_ID, @BINING_UPDATE = ISNULl(SHOP_ORDER_NUMBER,2), @LOT_DATEIN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	SELECT @DIE_BANK_BIN_UPDATE = Die_Bank_Bin_Update FROM Die_Bank_Bin_Update_Table

	Select @ISBOM_PART = ISNULL( PARAMETER_VALUE, 'FALSE') FROM PROCESS_PARAMETER WHERE Parameter_Key = 'IsBomPartNumber'  AND PROCESSSTEP_ID = @PROCESS_STEP_ID
	IF @ISBOM_PART = 'TRUE'
	BEGIN
			SET @P_TYPE = 'BOM'
	END
	ELSE
	BEGIN
		SELECT @P_TYPE = P_TYPE FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID
	END	

	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIN
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END
	DECLARE @GENERATE_SHIP_LOT_BY_WAFER_ID VARCHAR(5)
		
	SELECT @GENERATE_SHIP_LOT_BY_WAFER_ID = ISNULL(PROPERTY_VALUE,'FALSE') FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.operations.shippedLot.showShippedLotByWafer'

 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, Lot_Comments,LOT_DATE_CODE, PO_NUMBER) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @fab_lot_number, @VENDOR_LOTNUMBER, @WAFER_QTY_IN, @QTY_IN, @DATE_IN,@DATE_OUT, @STATUS, CURRENT_TIMESTAMP,@LOT_ENTERED_BY, @lot_Category, 0, @COMMENTS, @DATE_CODE, @PO_NUMBER )
		SELECT @LOT_ID = @@IDENTITY

		IF @DIE_BANK_BIN_UPDATE = 0
		BEGIN
			IF @WAFER_ID IS NULL OR @WAFER_ID= '' OR LEN(RTRIM(LTRIM(@WAFER_ID) ) ) = 0 OR @WAFER_ID <> '0'
			BEGIN	
				EXEC SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE	@PROCESS_STEP_ID, @LOT_ID, @LOT_NUMBER, @fab_lot_number, @QTY_IN, @WAFER_ID, @P_TYPE,@LOT_FROM
			END
		END
		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status, TimeStamp )
				VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @inventory_type, 'New Lot Notification', @wafer_qty_in, @qty_in, @wafer_id, 'Open', CURRENT_TIMESTAMP )
		
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_WAFER_QTY_IN, IL_QTY_IN, IL_DATE_IN, TIMESTAMP) VALUES (@LOT_ID, @WAFER_QTY_IN, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )		
	END
	ELSE
	BEGIN
		IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
		BEGIN
			IF @DIE_BANK_BIN_UPDATE = 0 AND @BINING_UPDATE = 0
			BEGIN
				EXEC SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE	@PROCESS_STEP_ID, @LOT_ID, @LOT_NUMBER, @fab_lot_number, @QTY_IN, @WAFER_ID, @P_TYPE,@LOT_FROM					
			END
		END
		ELSE
		BEGIN
			IF @DIE_BANK_BIN_UPDATE = 0 AND @BINING_UPDATE = 0 AND (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS <> 'Cancelled'  AND LOT_ID = @LOT_ID ) = 0 
			BEGIN
				EXEC SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE	@PROCESS_STEP_ID, @LOT_ID, @LOT_NUMBER, @fab_lot_number, @QTY_IN, @WAFER_ID, @P_TYPE,@LOT_FROM					
			END
		END	
		
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, WAFER_QTY_IN = @WAFER_QTY_IN, QTY_IN = @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DATE_CODE = @DATE_CODE, PO_NUMBER = @PO_NUMBER, LOT_ENTERED_BY = @LOT_ENTERED_BY, LOT_DROP_OUT = 0, Lot_Comments = @COMMENTS WHERE LOT_ID = @LOT_ID
	
		UPDATE INVENTORY_LOT SET IL_WAFER_QTY_IN = @WAFER_QTY_IN, IL_QTY_IN = @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
	END
		
	IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
	BEGIN
		/* Closing Transit Lots and Previous Process Lots By Wafer IDs*/
		Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS_BY_WAFER_ID @lot_number, @fab_lot_number, @QTY_IN, @DATE_IN,@WAFER_ID
	END
	ELSE
	BEGIN
		/* Closing Transit Lots and Previous Process Lots*/
		Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @fab_lot_number, @QTY_IN, @DATE_IN
	END
 END
